﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.IO;
using Microsoft.Office.Interop;
using Microsoft.Office.Core;
using DataTable = System.Data.DataTable;
using Excel = Microsoft.Office.Interop.Excel;
using Taramon.Exceller;
using ExamAnalysis.Model;



namespace ExamAnalysis.Core
{
    /// <summary>
    /// Util Class to deal with excel.
    /// </summary>
    public static class ExcelUtil
    {
        /// <summary>
        /// Read an excel file and return its first sheet as a <c>DataTable</c>
        /// </summary>

        public static int WriteExamItemsToExcel(List<ExamItems> ls, FileInfo excelfile, FileInfo outPutFile)
        {
            using (ExcelManager em = new ExcelManager())
            {
                em.Open(excelfile.FullName);
                em.ActivateSheet("作答信息录入");
                int row=3;
                foreach (ExamItems it in ls)
                {
                    em.SetValue(row, 1, it.ExamItemID);
                    em.SetValue(row, 2, it.ItemName);
                    em.SetValue(row, 3, it.ItemScore);
                    row++;
                }
                em.SaveAs(outPutFile.FullName);
            }
            return 0;
        }

        public static DataTable ReadExcelToTable(FileInfo excelfile)
        {
            if (excelfile.Exists == false)
                throw new UserException("文件不存在"); ;
            string path = excelfile.FullName;
            //连接字符串
            string type = excelfile.Extension;
            string connstring = "";
            // Office 07及以上版本 不能出现多余的空格 而且分号注意
            if (type == ".xlsx")
                connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
            //Office 07以下版本 因为本人用Office2010 所以没有用到这个连接字符串  可根据自己的情况选择 或者程序判断要用哪一个连接字符串
            else if (type == ".xls")
                connstring = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
            else throw new UserException("无法读取此格式文件");
            using (OleDbConnection conn = new OleDbConnection(connstring))
            {
                conn.Open();
                DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });  //得到所有sheet的名字
                string firstSheetName = sheetsName.Rows[0][2].ToString();   //得到第一个sheet的名字
                string sql = string.Format("SELECT * FROM [{0}]", firstSheetName);  //查询字符串
                OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
                DataSet set = new DataSet();
                ada.Fill(set);
                return set.Tables[0];
            }

        }

        /// <summary>
        /// Read an excel file and return specified sheet as a <c>DataTable</c>
        /// </summary>
        /// <param name="excelfile">the excel file</param>
        /// <param name="sheetname">sheet name</param>
        /// <returns></returns>
        public static DataTable ReadExcelToTable(FileInfo excelfile, string sheetname)
        {
            if (excelfile.Exists == false)
                throw new UserException("文件不存在");
            sheetname = sheetname + "$";
            string path = excelfile.FullName;
            //连接字符串
            string type = excelfile.Extension;
            string connstring = "";
            // Office 07及以上版本 不能出现多余的空格 而且分号注意
            if (type == ".xlsx")
                connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
            //Office 07以下版本 因为本人用Office2010 所以没有用到这个连接字符串  可根据自己的情况选择 或者程序判断要用哪一个连接字符串
            else if (type == ".xls")
                connstring = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
            else throw new UserException("无法读取此格式文件");
            using (OleDbConnection conn = new OleDbConnection(connstring))
            {
                conn.Open();
                string sql = string.Format("SELECT * FROM [{0}]", sheetname);  //查询字符串
                OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
                DataSet set = new DataSet();
                try
                {
                    ada.Fill(set);
                }
                catch (Exception e)
                {
                    throw new UserException(e.Message);

                }
                return set.Tables[0];
            }
        }

        public static int dataTableToSheet(string tableName,DataTable dataTable,FileInfo excel)
        {
            using (ExcelManager em = new ExcelManager())
            {
                em.Open(excel.FullName);
                em.ActivateSheet(tableName);
                em.RenameCurrentSheet("My Sheet");
                em.Save();
            }

            var excelApp = new Excel.Application();
            excelApp.Visible = true;
            Excel.Workbook wb = excelApp.Workbooks.Open(excel.FullName);
            Excel.Worksheet ws = wb.Worksheets.Add();
            ws.Name = tableName;
            for (int colunm = 0; colunm < dataTable.Columns.Count; colunm++)
                ws.Cells[1, colunm + 1] = dataTable.Columns[colunm].ColumnName;
            for(int row=0;row<dataTable.Rows.Count;row++)
                for(int column =0;column<dataTable.Columns.Count;column ++)
                    ws.Cells[row+2, column+1] = dataTable.Rows[row][column].ToString();
            wb.Close();
            excelApp.Quit();
            return 0;
        }
    }
}